﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Text;


namespace GPS.Controllers.GPS_Export
{
    public class GPS_DT_TN_BienBanTotNghiepController : Controller
    {
        //
        // GET: /GPS_DT_TN_BienBanTotNghiep/

        #region onit
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public void initialCategorySearchAction(View_Export_DT_BB_TN viewModel)
        {
            ViewBag.checkuser = this.getCurrentUser().MA_TINHTP;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, viewModel.pDVGTVT_MA);
            viewModel.pDVGTVT_MA = getdvgtvt_Ma;
        }
        #endregion

        #region Index
        public ActionResult Index(View_Export_DT_BB_TN SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            if (SearchModel.ExportButton == "Xuất Excel")
            {
                DataSet ds = GPS_DAOTAO_DTTN_BBTH_KQTN(SearchModel);
                if (ds != null)
                {
                    Export_DT_BB_TN _model = new Export_DT_BB_TN();
                    _model.TENSOGTVT = "NGHỆ AN";
                    _model.DATE = string.Format("ngày {0} tháng {1} năm {2}", DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());
                    _model.TIME = DateTime.Now.ToShortTimeString();
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        _model.DVGTVT_TEN = ds.Tables[0].Rows[i]["DVGTVT_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DVGTVT_TEN"].ToString();
                        _model.DDTTN_QD_SO_QD = ds.Tables[0].Rows[i]["DDTTN_QD_SO_QD"] == null ? "" : ds.Tables[0].Rows[i]["DDTTN_QD_SO_QD"].ToString();
                        if (ds.Tables[0].Rows[i]["DDTTN_QD_NGAY_QD"] != null)
                        {
                            _model.DDTTN_QD_NGAY_QD = Convert.ToDateTime(ds.Tables[0].Rows[i]["DDTTN_QD_NGAY_QD"].ToString());
                        }
                        _model.DS_KH_TEN = ds.Tables[0].Rows[i]["DS_KH_TEN"] == null ? "" : ds.Tables[0].Rows[i]["DS_KH_TEN"].ToString();
                    }

                    for (int i = 0; i < ds.Tables[2].Rows.Count; i++)
                    {
                        _model.TONGTHI = ds.Tables[2].Rows[i]["TONGTHI"] == null ? "" : ds.Tables[2].Rows[i]["TONGTHI"].ToString();
                        _model.THILAN1 = ds.Tables[2].Rows[i]["THILAN1"] == null ? "" : ds.Tables[2].Rows[i]["THILAN1"].ToString();
                        _model.THIKHOATRUOC = ds.Tables[2].Rows[i]["THIKHOATRUOC"] == null ? "" : ds.Tables[2].Rows[i]["THIKHOATRUOC"].ToString();
                        _model.THITHUCHANH = ds.Tables[2].Rows[i]["THITHUCHANH"] == null ? "" : ds.Tables[2].Rows[i]["THITHUCHANH"].ToString();
                        _model.DATTOTNGHIEP = ds.Tables[2].Rows[i]["DATTOTNGHIEP"] == null ? "" : ds.Tables[2].Rows[i]["DATTOTNGHIEP"].ToString();
                        _model.TRUOTTOTNGHIEP = ds.Tables[2].Rows[i]["TRUOTTOTNGHIEP"] == null ? "" : ds.Tables[2].Rows[i]["TRUOTTOTNGHIEP"].ToString();
                        _model.TRUOTLYTHUYET = ds.Tables[2].Rows[i]["TRUOTLYTHUYET"] == null ? "" : ds.Tables[2].Rows[i]["TRUOTLYTHUYET"].ToString();
                        _model.TRUOTTHUCHANH = ds.Tables[2].Rows[i]["TRUOTTHUCHANH"] == null ? "" : ds.Tables[2].Rows[i]["TRUOTTHUCHANH"].ToString();
                    }

                    _model.LISTpRetDSToChamThi = COMMON.Helpers.MapDataTableToList<Export_DT_BB_TN>(ds.Tables[1]);
                    _model.LISTpRetDSThiSinh = COMMON.Helpers.MapDataTableToList<Export_DT_BB_TN>(ds.Tables[3]);
                    byte[] contentFile = null;
                    contentFile = GPS.ENTITYS.EXPORTWORD_DT_BB_TN.EXPORT_DT_BB_TN(Server.MapPath("~/Templates/GPS_Word/BienBanTongHopTotNghiep.docx"), _model, "DocxGenericReport");
                    string nameOutput = "BienBanTongHopTotNghiep" + DateTime.Now.ToShortDateString() + ".docx";
                    return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
                }
            }
            return View(SearchModel);
        }
        #endregion
        public DataSet GPS_DAOTAO_DTTN_BBTH_KQTN(View_Export_DT_BB_TN item)
        {
            try
            {
                DataSet ds = OracleHelper.ExecuteDataset(_db.DbContext.Database.Connection.ConnectionString, CommandType.StoredProcedure, "GPS_DAOTAO.GPS_DAOTAO_DTTN_BBTH_KQTN"
                                                         , new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, string.IsNullOrWhiteSpace(item.pDVGTVT_MA) ? DBNull.Value : (object)item.pDVGTVT_MA, ParameterDirection.Input)
                                                         , new OracleParameter("pDTTN_QD_ID", OracleDbType.Int64, string.IsNullOrWhiteSpace(item.pQD_ID) ? DBNull.Value : (object)item.pQD_ID, ParameterDirection.Input)
                                                         , new OracleParameter("pRetHeader", OracleDbType.RefCursor, ParameterDirection.Output)
                                                         , new OracleParameter("pRetDSToChamThi", OracleDbType.RefCursor, ParameterDirection.Output)
                                                         , new OracleParameter("pRetTongHop", OracleDbType.RefCursor, ParameterDirection.Output)
                                                         , new OracleParameter("pRetDSThiSinh", OracleDbType.RefCursor, ParameterDirection.Output)
                                                        );

                return ds;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}